package com.link2room.optimus.service.impl;

import com.link2room.common.constant.DefaultValue;
import com.link2room.common.jpa.result.QueryResult;
import com.link2room.common.service.ICommonService;
import com.link2room.common.util.DateUtil;
import com.link2room.common.util.StringUtil;
import com.link2room.optimus.dto.param.api.MarketingQueryApiParamDto;
import com.link2room.optimus.entity.*;
import com.link2room.optimus.mapper.HotelMinPriceMapper;
import com.link2room.optimus.mapper.MarketingMapper;
import com.link2room.optimus.mapper.PriceStaticMapper;
import com.link2room.optimus.service.IMarketingService;
import com.link2room.soundwave.dto.result.base.PmsRmtypeAvailDto;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by sss on 2016/9/1.
 */
@Service("marketingService")
public class MarketingServiceImpl implements IMarketingService {
    @Resource
    private ICommonService commonService;

    @Override
    @Transactional
    public void saveMarketingGroupBase(MarketingCampaigns marketingCampaigns,
                                       List<MarketingCampaignsWeek> marketingCampaignsWeeks,
                                       List<MarketingCampaignsNorsv> marketingCampaignsNorsvs,
                                       List<MarketingCampaignsExdate> marketingCampaignsExdates) throws Exception {
        commonService.save(marketingCampaigns);
        commonService.saveList(marketingCampaignsWeeks);
        if(marketingCampaignsNorsvs!=null&&marketingCampaignsNorsvs.size()>0){
            commonService.saveList(marketingCampaignsNorsvs);
        }
        commonService.saveList(marketingCampaignsExdates);
    }

    @Override
    @Transactional
    public void updateMarketingGroupBase(MarketingCampaigns marketingCampaigns,
                                       List<MarketingCampaignsWeek> marketingCampaignsWeeks,
                                       List<MarketingCampaignsNorsv> marketingCampaignsNorsvs,
                                       List<MarketingCampaignsExdate> marketingCampaignsExdates) throws Exception {
        commonService.updateById(marketingCampaigns);
        if(marketingCampaignsWeeks!=null&&marketingCampaignsWeeks.size()>0) {
            commonService.updateList(marketingCampaignsWeeks);
            MarketingCampaignsExdate queryMce=new MarketingCampaignsExdate();
            queryMce.setLhotelGroupCode(marketingCampaigns.getLhotelGroupCode());
            queryMce.setMcCode(marketingCampaigns.getMcCode());
            commonService.deleteAllEQ(MarketingCampaignsExdate.class, queryMce);
            commonService.saveList(marketingCampaignsExdates);
        }
        if(marketingCampaignsNorsvs!=null&&marketingCampaignsNorsvs.size()>0){
            commonService.updateList(marketingCampaignsNorsvs);
        }
    }

    @Override
    @Transactional
    public void saveOrUpdateMarketingPbChannels(List<MarketingCampaignsChannel> marketingCampaignsChannels,
                                                List<MarketingCampaignsPrice> marketingCampaignsPrices) throws Exception {
        commonService.saveOrUpdateList(marketingCampaignsPrices);
        commonService.saveOrUpdateList(marketingCampaignsChannels);
    }

    /**
     * 查询满足条件活动列表
     * @param marketingQueryApiParamDto
     * @return
     * @throws Exception
     */
    @Override
    public QueryResult<MarketingMapper> getMarketingList(MarketingQueryApiParamDto marketingQueryApiParamDto)throws Exception{
        StringBuffer stringBuffer=new StringBuffer("SELECT " +
                "  a.lhotel_group_code lhotelGroupCode," +
                "  a.lhotel_code lhotelCode," +
                "  a.mc_code mcCode," +
                "  a.mc_name mcName," +
                "  a.mc_name_en mcNameEn," +
                "  a.price_code priceCode," +
                "  a.descript descript," +
                "  a.descript_en descriptEn," +
                "  a.descript_m descriptM," +
                "  a.descript_m_en descriptMEn," +
                "  a.start_date startDate," +
                "  a.use_real_avail useRealAvail," +
                "  a.end_date endDate," +
                "  a.rsv_start_date rsvStartDate," +
                "  a.rsv_end_date rsvEndDate," +
                "  a.mc_tag mcTag," +
                "  a.use_set_num useSetNum," +
                "  b.pb_channel pbChannel," +
                "  b.rp_code rpCode," +
                "  f.breakfast breakfast," +
                "  f.rp_name rpName," +
                "  f.min_adv_hours minAdvHours," +
                "  f.max_adv_hours maxAdvHours," +
                "  f.min_days minDays," +
                "  f.max_days maxDays," +
                "  f.start_time startTime," +
                "  f.end_time endTime," +
                "  f.res_type resType," +
                "  f.pay_time payTime," +
                "  f.remark remark," +
                "  f.coupon_rule couponRule," +
                "  f.pms_ratecode pmsRatecode" +
                " FROM " +
                "  marketing_campaigns AS a " +
                "  LEFT JOIN marketing_campaigns_channel AS b " +
                "    ON a.lhotel_group_code = b.lhotel_group_code " +
                "    AND a.mc_code = b.mc_code " +
                "  LEFT JOIN marketing_campaigns_hotel AS c " +
                "    ON c.lhotel_group_code = a.lhotel_group_code " +
                "    AND c.mc_code = a.mc_code " +
                "  LEFT JOIN l2r_rateplan AS f " +
                "    ON f.lhotel_group_code = a.lhotel_group_code " +
                "    AND f.rp_code = b.rp_code " +
                "  LEFT JOIN l2r_rateplan_hotel AS g " +
                "    ON g.lhotel_group_code = a.lhotel_group_code " +
                "    AND g.rp_code = f.rp_code " +
                "WHERE a.lhotel_group_code = ?1 " +
                "  AND a.start_date <= ?2 " +
                "  AND a.end_date >= ?3 " +
                "  AND a.rsv_start_date <= ?4 " +
                "  AND a.rsv_end_date >= ?5 " +
                "  AND a.is_halt = 'F' " +
                "  AND a.is_audit = 'T' " +
                "  AND b.pb_channel = ?6 " +
                "  AND b.is_halt = 'F' " +
                "  AND c.lhotel_code = ?7 " +
                "  AND c.is_halt = 'F' " +
                "  AND f.min_adv_hours <= ?8 " +
                "  AND f.max_adv_hours >= ?9 " +
                "  AND f.min_days <= ?10 " +
                "  AND f.max_days >= ?11 " +
                "  AND f.start_time <= ?12 " +
                "  AND f.end_time >= ?13 " +
                "  AND f.is_halt = 'F' " +
                "  AND g.is_halt = 'F' " +
                "  AND g.lhotel_code = ?14 ") ;
        int i=14;
        List objects=new ArrayList<>();
        objects.add(marketingQueryApiParamDto.getLhotelGroupCode());
        objects.add(DateUtil.format(new Date(),DateUtil.newFormat));
        objects.add(DateUtil.format(new Date(),DateUtil.newFormat));
        objects.add(marketingQueryApiParamDto.getCheckInDatetime());
        objects.add(marketingQueryApiParamDto.getCheckOutDatetime());
        objects.add(marketingQueryApiParamDto.getPbChannel());
        objects.add(marketingQueryApiParamDto.getLhotelCode());
        objects.add(marketingQueryApiParamDto.getAdvHours());
        objects.add(marketingQueryApiParamDto.getAdvHours());
        objects.add(marketingQueryApiParamDto.getStayDays());
        objects.add(marketingQueryApiParamDto.getStayDays());
        objects.add(marketingQueryApiParamDto.getTime());
        objects.add(marketingQueryApiParamDto.getTime());
        objects.add(marketingQueryApiParamDto.getLhotelCode());
        if(marketingQueryApiParamDto.getMcCodeList()!=null){
            stringBuffer.append("  AND a.mc_code IN (") ;
            for(String mcCode:marketingQueryApiParamDto.getMcCodeList()){
                objects.add(mcCode);
                i++;
                stringBuffer.append("?"+i+",");
            }
            stringBuffer.deleteCharAt(stringBuffer.length()-1);
            stringBuffer.append(" ) ");
        }

        stringBuffer.append("  AND " +
                "  (SELECT " +
                "    COUNT(*) " +
                "  FROM" +
                "    marketing_campaigns_exdate AS d " +
                "  WHERE d.lhotel_group_code = a.lhotel_group_code " +
                "    AND d.mc_code = a.mc_code " +
                "    AND d.exdate IN (") ;

        for(String usedDate: marketingQueryApiParamDto.getUsedateList()){
            objects.add(usedDate);
            i++;
            stringBuffer.append("?"+i+",");
        }
        stringBuffer.deleteCharAt(stringBuffer.length()-1);
        stringBuffer.append("    )) = 0 " +
                "  AND " +
                "  (SELECT " +
                "    COUNT(*) " +
                "  FROM" +
                "    marketing_campaigns_week AS e " +
                "  WHERE e.lhotel_group_code = a.lhotel_group_code " +
                "    AND e.mc_code = a.mc_code " +
                "    AND e.weekd IN (");
        for (Integer weekd: marketingQueryApiParamDto.getWeekds()){
            objects.add(weekd);
            i++;
            stringBuffer.append("?"+i+",");
        }
        stringBuffer.deleteCharAt(stringBuffer.length()-1);
        stringBuffer.append(") AND e.is_halt = 'T') = 0 ");
        String querySQL=stringBuffer.toString();
        return commonService.findByNative(querySQL, null, objects,objects, MarketingMapper.class, null, null);
    }

    /**
     * 查询房型基础卖价
     * @param lhotelGroupCode
     * @param lhotelCode
     * @param rmtype
     * @param checkInDate
     * @param checkOutDate
     * @return
     * @throws Exception
     */
    @Override
    public QueryResult<PriceStaticMapper> getRmtypeStaticPrice(String lhotelGroupCode, String lhotelCode, String priceCode,
                                                               String rmtype, String checkInDate, String checkOutDate)throws Exception{
        StringBuffer stringBuffer=new StringBuffer(
                "SELECT  " +
                        "  zz.date date, " +
                        "  zz.weekd weekd, " +
                        "  zz.lhotel_group_code lhotelGroupCode, " +
                        "  zz.lhotel_code lhotelCode, " +
                        "  zz.rmtype rmtype, " +
                        "  zz.price_code priceCode, " +
                        "  IFNULL( " +
                        "    dayprice, " +
                        "    IFNULL(wkprice, IFNULL(stprice, - 1000)) " +
                        "  ) price,  " +
                        "  zz.id id  " +
                        "FROM " +
                        "  (SELECT  " +
                        "    yy.*, " +
                        "    psd.price AS dayprice,  " +
                        "    psd.id AS id  " +
                        "  FROM " +
                        "    (SELECT  " +
                        "      xx.*, " +
                        "      psw.price AS wkprice  " +
                        "    FROM " +
                        "      (SELECT  " +
                        "        a.date, " +
                        "        a.weekd, " +
                        "        ps.lhotel_group_code, " +
                        "        ps.lhotel_code, " +
                        "        ps.rmtype, " +
                        "        ps.price_code, " +
                        "        ps.price AS stprice  " +
                        "      FROM " +
                        "        base_calendar a, " +
                        "        price_static ps  " +
                        "      WHERE  ps.lhotel_group_code = ?1  " +
                        "        AND ps.lhotel_code = ?2 " +
                        "        AND a.date>= ?3 " +
                        "        AND a.date< ?4 " );
        int i=4;
        if(StringUtil.isNotBlank(priceCode)){
            i++;
            stringBuffer.append(" AND ps.price_code= ?"+i+" ");
        }
        if(StringUtil.isNotBlank(rmtype)){
            i++;
            stringBuffer.append(" AND ps.rmtype= ?"+i+" ");
        }

        stringBuffer.append("  ) AS xx  LEFT JOIN price_static_week psw  " +
                "        ON ( " +
                "          xx.lhotel_group_code = psw.lhotel_group_code  " +
                "          AND xx.lhotel_code = psw.lhotel_code  " +
                "          AND xx.`rmtype` = psw.`rmtype`  " +
                "          AND xx.price_code = psw.price_code  " +
                "          AND xx.weekd = psw.weekd " +
                "        )) AS yy  " +
                "    LEFT JOIN `price_static_day` AS psd  " +
                "      ON ( " +
                "        yy.lhotel_group_code = psd.lhotel_group_code  " +
                "        AND yy.lhotel_code = psd.lhotel_code  " +
                "        AND yy.`rmtype` = psd.`rmtype`  " +
                "        AND yy.price_code = psd.price_code  " +
                "        AND yy.date = psd.`price_date` " +
                "      ) " +
                "       ) AS zz ");
        List objects=new ArrayList<>();
        objects.add(lhotelGroupCode);
        objects.add(lhotelCode);
        objects.add(checkInDate);
        objects.add(checkOutDate);
        if(StringUtil.isNotBlank(priceCode)){
            objects.add(priceCode);
        }
        if(StringUtil.isNotBlank(rmtype)){
            objects.add(rmtype);
        }
        String querySQL=stringBuffer.toString();
        return commonService.findByNative(querySQL, null, objects,objects, PriceStaticMapper.class, null, null);
    }

    /**
     * 查询酒店最低价
     * @param lhotelGroupCode
     * @param lhotelCode
     * @param checkInDate
     * @param checkOutDate
     * @param pbChannel
     * @return
     * @throws Exception
     */
    @Override
    public QueryResult<HotelMinPriceMapper> getHotelMinPrice(String lhotelGroupCode, String lhotelCode,
                                                             String checkInDate, String checkOutDate, String pbChannel)throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT  " +
                "  ap.date rsvDate, " +
                "  ap.lhotel_group_code lhotelGroupCode, " +
                "  ap.lhotel_code lhotelCode, " +
                "  ap.carry_type carryType, " +
                "  ap.pb_channel pbChannel, " +
                "  ap.ppprice minPrice  " +
                "FROM " +
                "  (SELECT  " +
                "    rack.*, " +
                "    mcp.`member_level`, " +
                "    mcp.`pool_code`, " +
                "    mcp.`pb_channel`, " +
                "    pp.multiply_value_sale, " +
                "    pp.add_value_sale, " +
                "    pp.carry_type, " +
                "    MIN( " +
                "      rack.rackprice * pp.`multiply_value_sale` + `add_value_sale` " +
                "    ) AS ppprice  " +
                "  FROM " +
                "    (SELECT  " +
                "      zz.*, " +
                "      IFNULL( " +
                "        dayprice, " +
                "        IFNULL(wkprice, IFNULL(stprice, - 1000)) " +
                "      ) AS rackprice  " +
                "    FROM " +
                "      (SELECT  " +
                "        yy.*, " +
                "        psd.`price` AS dayprice  " +
                "      FROM " +
                "        (SELECT  " +
                "          xx.*, " +
                "          psw.`price` AS wkprice  " +
                "        FROM " +
                "          (SELECT  " +
                "            a.date, " +
                "            a.weekd, " +
                "            mc.mc_code,    " +
                "            mc.`is_halt` as mc_ishalt, " +
                "            ps.lhotel_group_code, " +
                "            ps.lhotel_code, " +
                "            ps.`rmtype`, " +
                "            ps.price_code, " +
                "            ps.`price` AS stprice  " +
                "          FROM " +
                "            base_calendar a, " +
                "            `marketing_campaigns` mc, " +
                "            marketing_campaigns_detail mcd, " +
                "            `price_static` ps  " +
                "          WHERE mc.lhotel_group_code = ps.lhotel_group_code  " +
                "            AND mc.price_code = ps.price_code  " +
                "            AND mc.`mc_code`=mcd.`mc_code` " +
                "            AND mcd.`rmtype`=ps.`rmtype`  " +
                "            AND mcd.is_halt = 'F'   " +
                "            AND ps.`lhotel_group_code` = ?1  " +
                "            AND ps.`lhotel_code` = ?2  " +
                "            AND a.date >= ?3   " +
                "            AND a.date <= ?4   " +
                "            AND a.date >= mc.`rsv_start_date` " +
                "            AND a.date <= mc.`rsv_end_date`" +
                "            AND ps.is_halt='F') AS xx  " +
                "          LEFT JOIN price_static_week psw  " +
                "            ON ( " +
                "              xx.lhotel_group_code = psw.lhotel_group_code  " +
                "              AND xx.lhotel_code = psw.lhotel_code  " +
                "              AND xx.`rmtype` = psw.`rmtype`  " +
                "              AND xx.price_code = psw.price_code  " +
                "              AND xx.weekd = psw.weekd " +
                "            )) AS yy  " +
                "        LEFT JOIN `price_static_day` AS psd  " +
                "          ON ( " +
                "            yy.lhotel_group_code = psd.lhotel_group_code  " +
                "            AND yy.lhotel_code = psd.lhotel_code  " +
                "            AND yy.`rmtype` = psd.`rmtype`  " +
                "            AND yy.price_code = psd.price_code  " +
                "            AND yy.date = psd.`price_date` " +
                "          )) AS zz) AS rack, " +
                "    `marketing_campaigns_price` mcp, " +
                "    `marketing_campaigns_hotel` mch, " +
                "    `price_pool` pp  " +
                "  WHERE rack.mc_code = mcp.mc_code  " +
                "       AND mch.`lhotel_code`=rack.lhotel_code " +
                        "  AND mch.`mc_code`=rack.mc_code " +
                        "  AND mch.`is_halt`='F' "+
                "    AND mcp.`pool_code` = pp.`pool_code`  ");
        int i=5;
        if(StringUtil.isNotBlank(pbChannel)){
            sb.append("  AND mcp.pb_channel = ?"+i+"  ");
            i++;
        }
        sb.append("    AND rack.mc_ishalt='F'" +
                "    AND rack.mc_code=mcp.mc_code " +
                "    GROUP BY rack.date ) ap, " +
                "  (SELECT  " +
                "    nn.*, " +
                "    mcd.rmtype, " +
                "    mcd.is_halt AS mcdhalt  " +
                "  FROM " +
                "    (SELECT  " +
                "      mm.*, " +
                "      mce.is_halt AS mcehalt  " +
                "    FROM " +
                "      (SELECT  " +
                "        ll.*, " +
                "        mcw.is_halt AS mcwhalt  " +
                "      FROM " +
                "        (SELECT  " +
                "          a.date, " +
                "          a.weekd, " +
                "          mch.lhotel_group_code, " +
                "          mch.lhotel_code, " +
                "          mc.`mc_code`  " +
                "        FROM " +
                "          base_calendar a, " +
                "          `marketing_campaigns` mc, " +
                "          `marketing_campaigns_hotel` mch  " +
                "        WHERE mch.`lhotel_group_code` = ?"+i+"  ");
        i++;
        sb.append(
                "          AND mch.`lhotel_code` = ?"+i+"  ");
        i++;
        sb.append(
                "          AND mc.lhotel_group_code = mch.lhotel_group_code  " +
                "          AND mc.mc_code = mch.mc_code  " +
                "          AND a.date >= mc.`rsv_start_date`  " +
                "          AND a.date <= `rsv_end_date`  " +
                "          AND mc.`is_halt` = 'F'  " +
                "          AND mch.`is_halt` = 'F') AS ll,  " +
                "         `marketing_campaigns_week` mcw  " +
                "          WHERE ll.lhotel_group_code = mcw.lhotel_group_code  " +
                "          AND ll.mc_code = mcw.mc_code  " +
                "          AND ll.weekd = mcw.weekd) AS mm  " +
                "      LEFT JOIN `marketing_campaigns_exdate` mce  " +
                "        ON mce.`lhotel_group_code` = mm.lhotel_group_code  " +
                "        AND mce.`lhotel_code` = mm.lhotel_code  " +
                "        AND mce.`mc_code` = mm.`mc_code`  " +
                "        AND mce.`exdate` = mm.date) nn, " +
                "    `marketing_campaigns_detail` mcd  " +
                "  WHERE nn.lhotel_group_code = mcd.lhotel_group_code  " +
                "    AND nn.lhotel_code = mcd.lhotel_code  " +
                "    AND nn.mc_code = mcd.mc_code  " +
                "    AND nn.date >= ?"+i+"  ");
        i++;
        sb.append(
                "    AND nn.date <= ?"+i+"  ");
        i++;
        sb.append(
                "    AND nn.mcehalt IS NULL  " +
                "    AND mcd.is_halt = 'F'  " +
                "    AND nn.mcwhalt = 'F'  " +
                "  ORDER BY DATE, " +
                "    lhotel_code, " +
                "    mc_code, " +
                "    rmtype) mr  " +
                "WHERE ap.mc_code = mr.mc_code  " +
                "  AND ap.date = mr.date  " +
                "  AND ap.rmtype = mr.rmtype  " +
                "  AND ap.lhotel_code = mr.lhotel_code  " +
                        " GROUP BY ap.date,ap.pb_channel   " +
                " ORDER BY ap.date, " +
                "  ap.rmtype, " +
                "  ap.member_level ");
        List objects=new ArrayList<>();
        objects.add(lhotelGroupCode);
        objects.add(lhotelCode);
        objects.add(checkInDate);
        objects.add(checkOutDate);
        if(StringUtil.isNotBlank(pbChannel)){
            objects.add(pbChannel);
        }
        objects.add(lhotelGroupCode);
        objects.add(lhotelCode);
        objects.add(checkInDate);
        objects.add(checkOutDate);

        String querySQL=sb.toString();
        return commonService.findByNative(querySQL, null, objects,objects, HotelMinPriceMapper.class, null, null);
    }

    @Override
    public Integer getMinSaleNum(String useRealAvail,
                                 String useSetNum,
                                 Integer minSaleNum,
                                 Integer setNum,
                                 MarketingCampaignsStock mcs,
                                 MarketingCampaignsStockSold mcsd,
                                 PmsRmtypeAvailDto roomAvail)throws Exception{
        int saleNum=this.getSaleNum(useRealAvail,useSetNum,setNum, mcs, mcsd,roomAvail);
        if(minSaleNum>saleNum){
            minSaleNum=saleNum;
        }
        return minSaleNum;
    }

    @Override
    public Integer getSaleNum(String useRealAvail,
                              String useSetNum,
                              Integer setNum,
                              MarketingCampaignsStock mcs,
                              MarketingCampaignsStockSold mcsd,
                              PmsRmtypeAvailDto roomAvail)throws Exception{
        int avail;
        int saleNum;
        if(DefaultValue.F.equals(useRealAvail)){
            avail=roomAvail.getSaleNum();
        }else {
            avail=roomAvail.getAvail();
        }
        if(DefaultValue.T.equals(useSetNum)){

            int soldNum=0;
            if(mcs!=null){
                setNum=mcs.getSetNum();
            }
            if(mcsd!=null){
                soldNum=mcsd.getCurrentSoldNum();
            }
            saleNum=setNum-soldNum;
            if(saleNum>avail){
                saleNum=avail;
            }
        }else {
            saleNum=avail;
        }
        return saleNum;
    }


}
